﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace smp.QueryDb
{
    public class SqlAdapter : IDbAdapter
    {
        public string ParameterPix
        {
            get { return "@"; }
        }

        public IDbConnection CreateConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }

        public ICommand GeneratePagedQuery(string columnStatement, string fromStatement, string whereStatement, string orderStatement, int page, int pageSize, IList<IParameter> parameters)
        {
            if(string.IsNullOrEmpty(orderStatement))
            {
                throw Helper.Error("Cannot execute SqlAdapter paged query without at least one order by statement.");
            }

            var pageStart = (page*pageSize) - pageSize + 1;
            var pageEnd = page*pageSize;
            var pageStartParameter = new Parameter(ParameterPix + "pagestart", DbType.Int32, pageStart);
            var pageEndParameter = new Parameter(ParameterPix + "pageend", DbType.Int32, pageEnd);

            var command = new Command();
            command.CommandBuilder.AppendFormat(
                "SELECT * FROM (SELECT Row_Number() OVER(ORDER BY {0}) AS RowID, {1} FROM {2} ",
                orderStatement,
                columnStatement,
                fromStatement);

            if(!string.IsNullOrEmpty(whereStatement))
            {
                command.CommandBuilder.AppendFormat(" WHERE {0} ", whereStatement);
            }

            command.CommandBuilder.AppendFormat(") As Results WHERE RowID BETWEEN {0} AND {1}", pageStartParameter.Name, pageEndParameter.Name);
            if (parameters != null)
            {
                command.AddParameters(parameters);
            }
            command.AddParameter(pageStartParameter);
            command.AddParameter(pageEndParameter);

            return command;
        }
    }
}
